
USE [ppjdb]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[svc].[uspGetCouponInfo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [svc].[uspGetCouponInfo]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
  -----------------------------------------------------------------------
  File Name		:
  Description	:	Returns Coupon data from dbo.tblCoupon table base 
	on value of @biCouponID parameter
  Author        :   Sergey Morozov
  Copyright     :   (c) 2012 PPJ
  Incept		:	08/25/2012
  -----------------------------------------------------------------------
  Description/Purpose:

  Output values:
	@iRetCode =  0, sucess,
	@iRetCode = -1, SQL update error, detail info recorded into dbo.tblSQLAudit table
	@iRetCode = -2, Parameter @biCouponID is NULL or zero
	@iRetCode = -3, No record found in dbo.tblCoupon for CouponID {0}
	@iRetCode = -4, Store for StoreID {0} has "revoke" status
	@iRetCode = -5, Store domain for StoreIPListID {0} has "revoke" status

  -----------------------------------------------------------------------
  Change Log:
	Author			Date         Change
  -----------------------------------------------------------------------
  Sergiy Morozov 08/25/2012		development
  
  -----------------------------------------------------------------------
  Sample Script
  -----------------------------------------------------------------------

	declare @biCouponID [bigint]
		,@iRetCode [int]
		,@szMsgTier1 [nvarchar] (2048)
		,@szMsgTier2 [nvarchar] (1024)

	select @biCouponID = 2

	exec [svc].[uspGetCouponInfo]
		@biCouponID
		,@iRetCode output
		,@szMsgTier1 output   
		,@szMsgTier2 output  

	select 	
		@iRetCode as [RetCode] 
		,@szMsgTier1 as [RetMsg1]   
		,@szMsgTier2 as [RetMsg2]   		

*/

CREATE PROCEDURE [svc].[uspGetCouponInfo]
	@biCouponID [bigint]
    ,@iRetCode [int] output
    ,@szMsgTier1 [nvarchar] (2048) output   
    ,@szMsgTier2 [nvarchar] (1024) output  
AS
BEGIN

SET NOCOUNT ON;

DECLARE @iIsProcessComplete int
, @szProcessName VARCHAR(50)
, @ErrorMessage nvarchar(2048) 
, @ErrorNumber int
, @ErrorSeverity int
, @ErrorState int
, @ErrorProcedure nvarchar(2048)
, @ErrorLine int
, @TranCounter int
, @RowCount int
, @Msg nvarchar(4000)
, @iJobSetID int
, @szInsertedBy varchar(16)
, @szErrSubst [varchar] (100)

-- Get current stored proc name
SELECT @szProcessName = ISNULL(OBJECT_NAME(@@PROCID), 'uspGetCouponInfo')

BEGIN TRY
-----------------------------------
--BEGIN WORK
-----------------------------------

select @iRetCode = 0
	,@szMsgTier1 =''
	,@szMsgTier2=''
	,@szErrSubst = ''

declare @tbl table (CouponID [bigint] not null,
	StoreIPListID [bigint] not null,
	eCommCouponID [nvarchar] (64) not null,
	CouponDescription [nvarchar] (512) null,
	CouponValue [decimal] (8,3) null,
	MinOrderAmt [decimal] (7,2) not null,
	MaxOrderAmt [decimal] (7,2) not null,
	CouponType [int] not null,
	CouponTypeName [varchar] (64) not null,
	MsgToPopUp [nvarchar] (max) not null,
	ExpirationDate [datetime] null,
	LimitedNumber [bit] not null,
	Uses [int] null,
	RevokeStatus [bit] not null,
	DateRevoked [datetime] null,
	DateCreated [datetime] null,
	DateUpdated [datetime] null)	

declare @bTmpStoreStatus [bit]
	,@bTmpStoreIPStatus [bit]
	,@biStoreID [bigint]	
	,@biStoreIPListID [bigint]		

-- Validate parameters
if coalesce(@biCouponID,0)=0
-- Parameter is NULL or zero
	set @iRetCode = -2
else 	
 begin

	insert into @tbl (
		CouponID,
		StoreIPListID,
		eCommCouponID,
		CouponDescription,
		CouponValue,
		MinOrderAmt,
		MaxOrderAmt,
		CouponType,
		CouponTypeName,
		MsgToPopUp,
		ExpirationDate,
		LimitedNumber,
		Uses,
		RevokeStatus,
		DateRevoked,
		DateCreated,
		DateUpdated)
	select 
		c.CouponID,
		c.StoreIPListID,
		c.eCommCouponID,
		c.CouponDescription,
		c.CouponValue,
		c.MinOrderAmt,
		c.MaxOrderAmt,
		c.CouponType,
		vl.LookUpShortName,
		c.MsgToPopUp,
		c.ExpirationDate,
		c.LimitedNumber,
		c.Uses,
		c.RevokeStatus,
		c.DateRevoked,
		c.DateCreated,
		c.DateUpdated
		from dbo.tblCoupon c (nolock) 
		left join dbo.vwLookUp vl (nolock) 
			on vl.LookUpSubType = c.CouponType
			and vl.LookUpName = 'CouponType'		
		where c.CouponID = @biCouponID
	
	if @@ROWCOUNT = 0 
		select @iRetCode = -3
			,@szErrSubst = CAST(@biCouponID as [varchar] (30))
	else 
	 begin

 		select @bTmpStoreStatus = s.RevokeStatus 
			,@bTmpStoreIPStatus = sipl.RevokeStatus
			,@biStoreID = s.StoreID
			,@biStoreIPListID = sipl.StoreIPListID
			from dbo.tblStore s (nolock) 
			inner join dbo.tblStoreIPList sipl (nolock) on sipl.StoreID = s.StoreID
			inner join @tbl t on sipl.StoreIPListID = t.StoreIPListID		

		if coalesce(@bTmpStoreStatus,0) = 1
			select @iRetCode = -4
				,@szErrSubst = CAST(@biStoreID as [varchar] (30))
		else if coalesce(@bTmpStoreIPStatus,0) = 1
			select @iRetCode = -5
				,@szErrSubst = CAST(@biStoreIPListID as [varchar] (30))

		
	 end
 end

if @iRetCode = 0
 begin
 
	select 
		CouponID,
		StoreIPListID,
		eCommCouponID,
		CouponDescription,
		CouponValue,
		MinOrderAmt,
		MaxOrderAmt,
		CouponType,
		CouponTypeName,
		MsgToPopUp,
		ExpirationDate,
		LimitedNumber,
		Uses,
		RevokeStatus,
		DateRevoked,
		DateCreated,
		DateUpdated
		from @tbl 
	
 end
else
 begin
	select @szMsgTier1=replace(t.MsgTier1,'{0}',''''+@szErrSubst+''''),
		@szMsgTier2=replace(u.MsgTier2,'{0}',''''+@szErrSubst+''''),
		@iRetCode= case when u.[UIErrCode] is null then @iRetCode else t.[UIErrCode] end
		from [dbo].[tblMsgOutput] t (nolock)
		left join [dbo].[tblUIMessage] u (nolock) on u.[UIErrCode] = t.[UIErrCode]
		where t.SPName = @szProcessName
		and t.RetCode = @iRetCode
 end

 RETURN 0
END TRY
BEGIN CATCH
-- If failed rollback
-- get extended error information
	select @ErrorNumber  = ERROR_NUMBER(), @ErrorSeverity  = ERROR_SEVERITY(), @ErrorState = ERROR_STATE()
	, @ErrorProcedure  = ERROR_PROCEDURE(), @ErrorLine  = ERROR_LINE(), @ErrorMessage  = ERROR_MESSAGE()
	set @Msg = '; ErrorNumber = ' + cast(@ErrorNumber as varchar(4))
	+ ', ErrorSeverity  = '       + cast(@ErrorSeverity as varchar(4))
	+ ', ErrorState = '                 + cast(@ErrorState as varchar(4))
	+ ', ErrorProcedure = '       + cast(@ErrorProcedure as varchar(128))
	+ ', ErrorLine = '                  + cast(@ErrorLine as varchar(4))
	+ ', ErrorMessage = '         + cast(@ErrorMessage as varchar(2048))

	IF @TranCounter is not null
	 begin
		IF @TranCounter = 0 -- Transaction started in procedure.
		 ROLLBACK TRANSACTION;
		ELSE IF XACT_STATE() <> -1 -- roll back to the savepoint
		 ROLLBACK TRANSACTION uspGetCouponInfo;
	 end

	-- log error
	EXECUTE  dbo.uspRecordSQLAudit
	@biProcessID = -1
	,@iErrorNumber = @ErrorNumber
	,@iErrorSeverity = @ErrorSeverity
	,@iErrorState = @ErrorState
	,@szErrorProcedure = @ErrorProcedure
	,@iErrorLine = @ErrorLine
	,@szErrorMessage = @ErrorMessage
	,@szParameters = ''
	,@iInserted = 0

	select @iRetCode = -1 

	select @szMsgTier1=@ErrorMessage,
		@szMsgTier2=u.MsgTier2,
		@iRetCode= case when u.[UIErrCode] is null then @iRetCode else t.[UIErrCode] end
		from [dbo].[tblMsgOutput] t (nolock)
		left join [dbo].[tblUIMessage] u (nolock) on u.[UIErrCode] = t.[UIErrCode]
		where t.SPName = @szProcessName
		and t.RetCode = @iRetCode

RETURN -1
END CATCH

END
GO

if not exists (select 1 from dbo.tblMsgOutput (nolock) where SPName = 'uspGetCouponInfo')
insert into dbo.tblMsgOutput (SPName,RetCode,MsgTier1,UIErrCode)
select 'uspGetCouponInfo',-1, 'SQL update error',207
union all
select 'uspGetCouponInfo',-2, 'Parameter @biCouponID is NULL or zero',207
union all
select 'uspGetCouponInfo',-3, 'No record found in dbo.tblCoupon for CouponID {0}',207
union all
select 'uspGetCouponInfo',-4, 'Store for StoreID {0} has "revoke" status',207
union all
select 'uspGetCouponInfo',-5, 'Store domain for StoreIPListID {0} has "revoke" status',207
go